package com.sinopac.model.main;

import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import netfusion.jbranch.database.DataAccessManager;

import org.apache.commons.lang.StringUtils;

import com.sinopac.model.core.ModelCore;
import com.sinopac.tableBean.TbRoles;

public class MainModel extends ModelCore{
	public String errorMessage;
	
	public String errorMessageForSyn;
	
	public Boolean chkCustID(String custId){
		Boolean flag = false;
		if(StringUtils.isNotBlank(custId)){
			DataAccessManager dam = new DataAccessManager();
			String sql = "select * from Customers where customer_id = :custId ";
			dam.setQueryString(sql);
			dam.setObject("custId", StringUtils.trimToEmpty(custId));
			try {
				List<Map<String,Object>> list = new ArrayList<Map<String,Object>>();
				list = dam.exeQueryMap();
				if(list.isEmpty()){
					errorMessage = "查無此客戶ID,請重新輸入正確客戶ID。";
				}else{
					flag = !list.isEmpty();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}else{
			errorMessage = "查無此客戶ID,請重新輸入正確客戶ID。";
		}
		return flag;
	}
	
	public Boolean chkToken_esn(String token_esn){
		Boolean flag = false;
		if(StringUtils.isNotBlank(token_esn)){
			DataAccessManager dam = new DataAccessManager();
			String sql = "select * from Tokens where token_esn = :token_esn ";
			dam.setQueryString(sql);
			dam.setObject("token_esn", StringUtils.trimToEmpty(token_esn));
			try {
				List<Map<String,Object>> list = new ArrayList<Map<String,Object>>();
				list = dam.exeQueryMap();
				if(list.isEmpty()){
					errorMessage = "查無此TOKEN ESN,請重新輸入正確TOKEN ESN。";
				}else{
					flag = !list.isEmpty();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}else{
			errorMessage = "查無此TOKEN ESN,請重新輸入正確TOKEN ESN。";
		}
		return flag;
	}
	
	public boolean chkToken_Status(String token_esn){
		Boolean flag = false;
		if(StringUtils.isNotBlank(token_esn)){
			DataAccessManager dam = new DataAccessManager();
			String sql = "select * from Tokens where token_esn = :token_esn ";
			dam.setObject("token_esn", StringUtils.trimToEmpty(token_esn));
			dam.setQueryString(sql);
			try {
				List<Map<String,Object>> list = new ArrayList<Map<String,Object>>();
				list = dam.exeQueryMap();
				if(list.isEmpty()){
					errorMessageForSyn = "查無此TOKEN ESN,請重新輸入正確TOKEN ESN。";
				}else{
					dam = new DataAccessManager();
					StringBuffer query2 = new StringBuffer("select * from Tokens where  token_esn = :token_esn and token_status = '01'");
					dam.setObject("token_esn", StringUtils.trimToEmpty(token_esn));
					dam.setQueryString(query2.toString());
					
					List<Map<String,Object>> list2 = new ArrayList<Map<String,Object>>();
					list2 = dam.exeQueryMap();
					if(list2.isEmpty()){
						for(Map<String, Object> map1:list){
							flag = false;
							String status_ch = getToken_statusCH(map1.get("token_status").toString());
							errorMessageForSyn = "此TOKEN ESN狀態為："+(map1.get("token_status").toString())+"－"+status_ch+"，不可同步。";
						}
					}else{
						flag = !list.isEmpty();
					}
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}else{
			errorMessageForSyn = "查無此TOKEN ESN,請重新輸入正確TOKEN ESN。";
		}
		System.out.println("flag:"+flag);
		return flag;
	}
	
	/**
	 * 取得User的角色Roles
	 * @param 
	 * @return
	 */
	public String getRoles(String userId){
		String user_roles = "";
		DataAccessManager dam = new DataAccessManager();
//		SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		try {
			String sql = "SELECT * FROM Roles WHERE userid = '"+userId+"'";
			dam.setQueryString(sql);
			List<Map<String,Object>> list = dam.exeQueryMap();
			if(!list.isEmpty()){
				user_roles = list.get(0).get("user_roles").toString();			
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}		
		return user_roles;		
	}
	
	/**
	 * 取得User的username
	 * @param 
	 * @return
	 */
	public String getUserName(String userId){
		String user_name = "";
		DataAccessManager dam = new DataAccessManager();
//		SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		try {
			String sql = "SELECT * FROM Roles WHERE userid = '"+userId+"'";
			dam.setQueryString(sql);
			List<Map<String,Object>> list = dam.exeQueryMap();
			if(!list.isEmpty()){
				user_name = list.get(0).get("username").toString();			
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}		
		return user_name;		
	}
	
	
	private String getToken_statusCH(String token_status_no) {
		int no = Integer.parseInt(token_status_no);
		String status = "";
		switch (no) {
			case 0:
				status = "未啟用";
				break;
			case 1:
				status = "正常";
				break;
			case 2:
				status = "已暫禁";
				break;
			case 3:
				status = "已註銷";
				break;
			case 4:
				status = "異動中，待覆核";
				break;
		}
		return status;
	}
	
	/**
	 * 寫入LoginLog
	 * @return
	 */
	public Boolean insertLoginLog(String userId, String userRoles, String clientIp, String result){
//		Map<String,String> roleType = new HashMap<String, String>();
//		roleType.put("0", "");
//		roleType.put("1", "行員");
//		roleType.put("2", "主管");
//		roleType.put("3", "查詢");
//		roleType.put("4", "前置");
//		roleType.put("9", "系統管理者");
		Date date = new Date();
		SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		String subdate = df.format(date);

		boolean flag = false;
		DataAccessManager dam = new DataAccessManager();
		String sql = "INSERT INTO LoginLog(UserID, UserName, RoleType, " +
					 "Source, HostIP, LogDateTime, Result, Memo) "+ 
	                 "VALUES(:UserID, :UserName, :RoleType, :Source, :HostIP, :LogDateTime, :Result, :Memo)";
		dam.setQueryString(sql);
		dam.setObject("UserID", userId);
		dam.setObject("UserName", getUserName(userId));
		dam.setObject("RoleType", userRoles);
		dam.setObject("Source", "02");
		dam.setObject("HostIP", clientIp);
		dam.setObject("LogDateTime", subdate);
		dam.setObject("Result", result);
		dam.setObject("Memo", "");
		try {
			flag = dam.exeUpdate()>0;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return flag;
	}
	
	@SuppressWarnings("unchecked")
	public Map<String,String> getTestList(){
		Map<String,String> result = new HashMap<String, String>();
		String sql = "select * from Roles order by user_roles ";
		List<TbRoles> list = exeQuery(sql, TbRoles.class);
		for(TbRoles vo : list){
			result.put(vo.getUserid(), getText(USER_ROLES, vo.getUserRoles())+":" + vo.getUsername());
		}
		return result;
	}

	public String getErrorMessage() {
		return errorMessage;
	}
	
	public String getErrorMessageForSyn(){
		return errorMessageForSyn;
	}
}
